﻿Public Class ModelForm

    Dim ItsMine As Boolean

    Dim CurrentProduct As String = ""



    Private Sub ModelForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadAllocationsLineGrid()
        LoadViewByBox()
        'LoadScenarioBox()
        'LoadProducts()

        SetupCurrentScenario()

        'If CurrentUser.Level = 5 Then
        '    CasesButton.Enabled = True
        'Else
        '    CasesButton.Enabled = False

        'End If

        'If CurrentUser.Level > 3 Then
        '    '  ReportDesignerBut.Enabled = True
        '    ApplyButton.Enabled = True
        '    ScenFolderBut.Enabled = True
        'Else
        '    '  ReportDesignerBut.Enabled = False
        '    ApplyButton.Enabled = False
        '    ScenFolderBut.Enabled = False
        'End If

        If LoadPeriodBox(PeriodBox) Then
            PeriodBox.SelectedValue = CurrentPeriodID

        End If

        If UserFormCheck(Me.Name) = "R" Then

            Button1.Enabled = False
            Button2.Enabled = False
            DeleteScenarioBut.Enabled = False
            ScenFolderBut.Enabled = False
            PublishBut.Enabled = False
            ReportDesignerBut.Enabled = False
            ApplyButton.Enabled = False
            AddBut.Enabled = False
            CasesButton.Enabled = False
            EditBut.Enabled = False
            DeleteBut.Enabled = False
            AddProdBut.Enabled = False
            ProductEditBut.Enabled = False
            ProdDelBut.Enabled = False
            AddFolderBut.Enabled = False
            DeleteFolderBut.Enabled = False
            CreateDataCutsToolStripMenuItem.Enabled = False
            MessageBox.Show("Currently you only have read only access for the Modelling section." + vbNewLine + "Create and edit functions have been disabled.", "Access Rights",
                            MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If

        Panel3.Height = MainForm.Height / 2.5
    End Sub

    Private Sub LoadViewByBox()
        Dim TheView As DataView

        TheView = LoadDataView("select '<My>' as ID, 'My Scenarios' as Description Union " & _
                                "select '<Published>' as ID, 'Published Scenarios' as Description Union " & _
                                "select distinct folder as ID, folder as Description from dbo.Modelling_Scenarios where folder is not null and (Published = 1 or User_Name = '" & CurrentUser.Name & "')")

        ViewbyBox.DataSource = TheView

        ViewbyBox.ValueMember = "ID"
        ViewbyBox.DisplayMember = "Description"



    End Sub

    Private Sub LoadScenarioBox()
        Job("Loading Scenarios...")
        Dim TheData As DataView
        Dim sql As String

        If ViewbyBox.SelectedValue.ToString = "System.Data.DataRowView" Then
            ProductsPanel.Enabled = False
            Exit Sub
        End If


        If ViewbyBox.SelectedValue.ToString = "<My>" Then
            sql = "Select *, rtrim(Scenario_Description) as Description from Modelling_Scenarios where User_Name = '" & CurrentUser.Name & "'"
        Else
            If ViewbyBox.SelectedValue.ToString = "<Published>" Then
                sql = "Select *, rtrim(Scenario_Description) + ' (' + User_Name + ')' as Description from Modelling_Scenarios where Published = 1 "
            Else
                sql = "Select *, rtrim(Scenario_Description) + ' (' + User_Name + ')' as Description from Modelling_Scenarios where Folder = '" & ViewbyBox.SelectedValue.ToString & "' " & _
                      "and (Published = 1 or User_Name = '" & CurrentUser.Name & "')"
            End If
        End If

        


        TheData = LoadDataView(sql)

        ScenarioBox.DataSource = TheData
        ScenarioBox.ValueMember = "Scenario_ID"
        ScenarioBox.DisplayMember = "Description"

        ProductsPanel.Enabled = False

        If TheData.Count > 0 Then
            ProductsPanel.Enabled = True
            SetToolButtons(True)
        End If

        ScenarioBox.Refresh()
    End Sub

    Private Sub LoadAllocationsLineGrid()
        'Load the Allocation Lines Grid
        Job("Loading Allocation Lines...")
        Dim TheData As DataView
        Dim sql As String

        If HideLinesBox.Checked And ScenarioBox.Items.Count > 0 Then

            sql = "select Budget_ID, Budget_id as ID, Budget_Description as Description, 1 as Type from Budgets " & _
                    "where Budget_ID in (select Budget_ID from Allocation_Lines where Allocation_ID in (select distinct Allocation_ID from Modelling_Stages " & _
                    "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "')) " & _
                    "union all  " & _
                    "select Budget_id, Allocation_id as ID, Allocation_Description as Description, 2 as Type from Allocation_Lines where Hidden = 0  " & _
                    "and Allocation_ID in (select distinct Allocation_ID from Modelling_Stages " & _
                    "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "') " & _
                    "order by Budget_id, Type, ID "
        Else
            sql = "select Budget_ID, Budget_id as ID, Budget_Description as Description, 1 as Type from Budgets " & _
              "union all " & _
              "select Budget_id, Allocation_id as ID, Allocation_Description as Description, 2 as Type from Allocation_Lines where Hidden = 0 " & _
              "order by Budget_id, Type, ID"

        End If

        
        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then
            sql = "select Budget_ID, Budget_id as ID, Budget_Description as Description, 1 as Type from Budgets " & _
              "union all " & _
              "select Budget_id, Allocation_id as ID, Allocation_Description as Description, 2 as Type from Allocation_Lines where Hidden = 0 " & _
              "order by Budget_id, Type, ID"

            TheData = LoadDataView(sql)
        End If

        AllocationLinesGrid.DataSource = TheData

        'hide the unsed columns
        AllocationLinesGrid.Columns("Budget_ID").Visible = False
        AllocationLinesGrid.Columns("ID").Visible = False
        AllocationLinesGrid.Columns("Type").Visible = False

        Dim i As Integer

        For i = 0 To AllocationLinesGrid.Columns.Count - 1

            AllocationLinesGrid.Columns.Item(i).SortMode = DataGridViewColumnSortMode.NotSortable

        Next

        AllocationLinesGrid.ColumnHeadersVisible = False
        If AllocationPanel.Width < AllocationLinesGrid.Columns("Description").Width + 20 Then
            AllocationPanel.Width = AllocationLinesGrid.Columns("Description").Width + 20

        End If


    End Sub

    Private Sub AllocationLinesGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles AllocationLinesGrid.CellFormatting
        With AllocationLinesGrid

            If .Item(.Columns("Type").Index, e.RowIndex).Value = 1 Then
                e.CellStyle.BackColor = Color.LightGray
                e.CellStyle.Font = New Font("Arial", 8.25, FontStyle.Bold)

            End If

        End With
    End Sub

    Private Sub SetStageButtons()
        Try
            With AllocationLinesGrid
                If .Item(.Columns("Type").Index, .CurrentRow.Index).Value = 1 Then
                    SelectedLabel.Text = "Please select an allocation line from the list on the left"
                    StagesGrid.DataSource = Nothing
                    StagesGrid.Refresh()

                    ButtonsPanel.Enabled = False

                Else
                    SelectedLabel.Text = .Item(.Columns("Description").Index, .CurrentRow.Index).Value.ToString & " Stages"
                    LoadStages()
                    ButtonsPanel.Enabled = True

                    If ScenarioBox.SelectedIndex = -1 Then
                        ButtonsPanel.Enabled = False
                    End If

                End If
            End With
        Catch ex As Exception
            'not ready yet
        End Try
        

    End Sub

    Private Sub AllocationLinesGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles AllocationLinesGrid.CellEnter
        SetStageButtons()

    End Sub

    Private Sub AddBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddBut.Click
        AddStage()

    End Sub

    Private Sub LoadProducts()
        Job("Loading Products...")
        Dim TheData As DataView
        Dim sql As String

        ' ButtonsPanel.Enabled = False

        ProductGrid.DataSource = Nothing
        ProductGrid.Rows.Clear()
        ProductGrid.Refresh()

        If ScenarioBox.Items.Count = 0 Then Exit Sub


        'to solve the null for some reason I'm adding an error catch

        On Error GoTo ErrorCatch



        sql = "select Scenario_ID, Product_Number, Product_Description as Name " & _
              "from Modelling_Products " & _
              "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

        TheData = LoadDataView(sql)

        ProductGrid.DataSource = TheData

        ProductGrid.Columns("Scenario_ID").Visible = False
        ProductGrid.Columns("Product_Number").Visible = False

        If ProductGrid.RowCount <> 0 Then
            ProductGrid.CurrentCell = ProductGrid.Item(2, 0)
        End If

        LoadProductsView()

        Job("Ready")

ErrorCatch:
        Exit Sub
        Job("Ready")
    End Sub


    Private Sub LoadProductsView()
        'load the view


        Dim Anode As TreeNode
        Dim arow As DataRowView
        Dim afolder As DataRowView
        Dim TheData As DataView
        Dim FolderView As DataView
        Dim sql As String

        'first add the folders
        ProductView.Nodes.Clear()
        If ScenarioBox.SelectedValue = Nothing Then Exit Sub

        'add a root folder node
        With ProductView
            Anode = .Nodes.Add("NF", "New and Unsorted Products", 2, 2)

            'add the no folder products first
            sql = "select Scenario_ID, Product_Number, Product_Description as Name " & _
             "from Modelling_Products " & _
             "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Folder_ID is null order by Product_Description"

            TheData = LoadDataView(sql)

            For Each arow In TheData
                With ProductView
                    Anode.Nodes.Add(arow.Item("Product_Number").ToString, arow.Item("Name").ToString, 3, 3)

                End With

            Next

            'now add each folder that might exist
            'and then for each of those folders add the products

            'get the highest level you have
            sql = "select max(TheLevel) as TheLevel from Modelling_Product_Folders where scenario_id = '" & ScenarioBox.SelectedValue.ToString & "'"
            Dim TheLevel As Integer
            Dim l As Integer
            Dim TheParentNode As TreeNode
            FolderView = LoadDataView(sql)

            If FolderView.Count = 0 Then
                TheLevel = 0
            Else
                If IsDBNull(FolderView(0).Item("TheLevel")) Then
                    TheLevel = 0
                Else
                    TheLevel = FolderView(0).Item("TheLevel")
                End If

            End If

            If TheLevel <> -1 Then

                For l = 0 To TheLevel

                    sql = "select * from Modelling_Product_Folders where scenario_id = '" & ScenarioBox.SelectedValue.ToString & "' and TheLevel = " & l
                    FolderView = LoadDataView(sql)

                    For Each afolder In FolderView

                        If Not IsDBNull(afolder.Item("Parent_ID")) Then
                            Dim tn() As TreeNode = ProductView.Nodes.Find(afolder.Item("Parent_ID"), True)
                            Dim i As Integer = 0
                            If l <> 0 Then

                                Do While (i < tn.Length)
                                    ProductView.SelectedNode = tn(i)
                                    '  treeView1.SelectedNode.BackColor = Color.Yellow
                                    i = (i + 1)
                                Loop
                            End If
                        End If

                        TheParentNode = ProductView.SelectedNode

                        If l = 0 Then
                            Anode = .Nodes.Add(afolder.Item("Folder_ID"), afolder.Item("Folder_Description"), 2, 2)
                        Else
                            Anode = TheParentNode.Nodes.Add(afolder.Item("Folder_ID"), afolder.Item("Folder_Description"), 2, 2)
                        End If


                        sql = "select Scenario_ID, Product_Number, Product_Description as Name " & _
                     "from Modelling_Products " & _
                     "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Folder_ID = '" & Anode.Name & "' order by Product_Description"

                        TheData = LoadDataView(sql)

                        For Each arow In TheData
                            With ProductView
                                Anode.Nodes.Add(arow.Item("Product_Number").ToString, arow.Item("Name").ToString, 3, 3)

                            End With

                        Next

                    Next

                Next

            End If

        End With

        If CurrentProduct <> "" Then
            Dim tn() As TreeNode = ProductView.Nodes.Find(CurrentProduct, True)
            Dim i As Integer = 0
            Do While (i < tn.Length)
                ProductView.SelectedNode = tn(i)
                '  treeView1.SelectedNode.BackColor = Color.Yellow
                i = (i + 1)
            Loop
        End If

    End Sub

    Private Sub LoadStages()
        Job("Loading Stages...")
        Dim TheData As DataView
        Dim sql As String

        ' ButtonsPanel.Enabled = False

        If AllocationLinesGrid.RowCount = 0 Then
            Job("Ready")
            Exit Sub
        End If


        If ScenarioBox.Items.Count = 0 Then
            Job("Ready")
            Exit Sub
        End If


        If AllocationLinesGrid.SelectedRows.Count = 0 Then
            Job("Ready")
            Exit Sub
        End If


        'to solve the null for some reason I'm adding an error catch

        On Error GoTo ErrorCatch

      

        sql = "select Scenario_ID, Allocation_ID, Stage_Number, 'Stage ' + cast(Stage_Number as varchar) as Stage, Stage_Description as Name " & _
              "from Modelling_Stages " & _
              "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and " & _
              "Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'"


        TheData = LoadDataView(sql)

        StagesGrid.DataSource = TheData

        StagesGrid.Columns("Scenario_ID").Visible = False
        StagesGrid.Columns("Allocation_ID").Visible = False
        StagesGrid.Columns("Stage_Number").Visible = False

        StagesGrid.CurrentCell = StagesGrid.Item(3, 0)

        'only allow the last line to be deleted
        If StagesGrid.RowCount = 0 Then Exit Sub

        If StagesGrid.CurrentRow.Index = StagesGrid.RowCount - 1 Then
            DeleteBut.Enabled = True

        Else
            DeleteBut.Enabled = False

        End If
        Job("Ready")
ErrorCatch:
        Exit Sub
        Job("Ready")
    End Sub

    Private Sub AddStage()
        Dim sql As String

        sql = "insert into Modelling_Stages " & _
              "(Scenario_ID, Allocation_ID, Stage_Number, Stage_Description) " & _
              "values ('" & ScenarioBox.SelectedValue.ToString & "', '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "', " & _
               StagesGrid.RowCount + 1 & ",'')"

        RunNonQuery(sql)

      
        StageForm.CurrentStage = StagesGrid.RowCount + 1

        With AllocationLinesGrid
            StageForm.Text = .Item(.Columns("Description").Index, .CurrentCell.RowIndex).Value.ToString & " - Stage " & StageForm.CurrentStage.ToString
            StageForm.StageHeader = StageForm.Text

        End With
        'Stage Form Docking to Main Form
        StageForm.Who = "Stage"
        StageForm.WarningLabel.Text = "Please enter a Stage Name"
        StageForm.MdiParent = MainForm
        StageForm.Dock = DockStyle.Fill
        StageForm.Show()

        LoadStages()
    End Sub

    Private Sub AddProduct()
        Dim sql As String
        Dim ProductNumber As Integer
        Dim TheView As DataView

        'find the next product number

        sql = "select coalesce(max(Product_Number),0) as ProdNum from Modelling_Products " & _
              "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

        TheView = LoadDataView(sql)

        ProductNumber = TheView(0).Item("ProdNum") + 1

        TheView = Nothing

        sql = "insert into Modelling_Products " & _
                  "(Scenario_ID, Product_Number, Product_Description) " & _
                  "values ('" & ScenarioBox.SelectedValue.ToString & "',  " & _
                   ProductNumber.ToString & ",'')"

        RunNonQuery(sql)

        StageForm.CurrentStage = ProductNumber

        StageForm.Text = "New Product"
        StageForm.StageHeader = StageForm.Text

        'Stage Form Docking to Main Form
        StageForm.Who = "Product"
        StageForm.WarningLabel.Text = "Please enter a Product Name"
        StageForm.DescriptionBox.Focus()

        StageForm.MdiParent = MainForm
        StageForm.Dock = DockStyle.Fill
        StageForm.Show()


        

        LoadProducts()

        Dim tn() As TreeNode = ProductView.Nodes.Find(ProductNumber.ToString, True)
        Dim i As Integer = 0
        Do While (i < tn.Length)
            ProductView.SelectedNode = tn(i)
            '  treeView1.SelectedNode.BackColor = Color.Yellow
            i = (i + 1)
        Loop


    End Sub
  

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If ScenariosDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim sql As String
            sql = "insert into Modelling_Scenarios (Scenario_ID, Scenario_Description, User_Name, Published) values('" & ScenariosDialog.IDBox.Text & "', '" & ScenariosDialog.DescriptionBox.Text & "', '" & CurrentUser.Name & "', 0)"

            If RunNonQuery(sql) = "" Then
                MessageBox.Show("The scenario was successfuly created", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ViewbyBox.SelectedValue = "<My>"
                LoadScenarioBox()
                ScenarioBox.SelectedValue = ScenariosDialog.IDBox.Text

                AllocationLinesGrid.Refresh()


            Else
                MessageBox.Show("There was an error creating the scenario", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End If


        End If
    End Sub

    Private Sub SetupCurrentScenario()
        Dim Sql As String
        Dim TheView As DataView

        If ScenarioBox.SelectedIndex = -1 Then
            CreatedbyLabel.Text = ""
            PublishLabel.Text = ""
            PublishBut.Text = "Publish Scenario"
            PublishBut.Image = Images.Images(0)

            Exit Sub

        End If

        Sql = "Select * from Modelling_Scenarios where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

        TheView = LoadDataView(Sql)

        CreatedbyLabel.Text = ""
        PublishLabel.Text = ""
        PublishBut.Text = "Publish Scenario"
        PublishBut.Image = Images.Images(0)

        If TheView.Count = 0 Then Exit Sub

        CreatedbyLabel.Text = "This scenario was created by " & TheView(0).Item("User_Name").ToString

        If CurrentUser.Name.ToUpper = TheView(0).Item("User_Name").ToString.ToUpper Then

            ItsMine = True
        Else
            ItsMine = False
        End If

        If ItsMine Then
            PublishBut.Enabled = True
            ProductEditBut.Text = "Edit"
            EditBut.Text = "Edit"
            DeleteScenarioBut.Enabled = True
            ProdDelBut.Enabled = True
            DeleteBut.Enabled = True
            AddProdBut.Enabled = True
            AddBut.Enabled = True
            ProductView.AllowDrop = True
            ProductView.LabelEdit = True
            AddFolderBut.Enabled = True
            DeleteFolderBut.Enabled = True



            If TheView(0).Item("Published").ToString.Length <> 0 Then
                If TheView(0).Item("Published") = 1 Then
                    PublishLabel.Text = "This scenario can be viewed (but not edited) by all authorised users"
                    PublishBut.Image = Images.Images(1)
                    PublishBut.Text = "Remove Publishing"

                Else
                    PublishLabel.Text = "This scenario can only be viewed and edited by you"
                    PublishBut.Image = Images.Images(0)
                    PublishBut.Text = "Publish Scenario"
                End If
            Else
                PublishLabel.Text = "This scenario can only be viewed and edited by you"
                PublishBut.Image = Images.Images(0)
                PublishBut.Text = "Publish Scenario"
            End If
        Else
            PublishBut.Enabled = False
            ProductEditBut.Text = "View"
            EditBut.Text = "View"
            DeleteScenarioBut.Enabled = False
            ProdDelBut.Enabled = False
            DeleteBut.Enabled = False
            AddProdBut.Enabled = False
            AddBut.Enabled = False
            ProductView.AllowDrop = False
            ProductView.LabelEdit = False
            AddFolderBut.Enabled = False
            DeleteFolderBut.Enabled = False

            PublishLabel.Text = "This scenario can be viewed (but not edited) by all authorised users"
            PublishBut.Image = Images.Images(1)
            PublishBut.Text = "Remove Publishing"
        End If
    End Sub

    Private Sub ScenarioBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ScenarioBox.SelectedIndexChanged
        LoadAllocationsLineGrid()

        SetupCurrentScenario()

        LoadStages()
        LoadProducts()

    End Sub

    Private Sub EditStage()
        If StagesGrid.RowCount = 0 Then Exit Sub

        StageForm.CurrentStage = StagesGrid.CurrentRow.Index + 1

        With AllocationLinesGrid
            StageForm.Text = .Item(.Columns("Description").Index, .CurrentCell.RowIndex).Value.ToString & " - Stage " & StageForm.CurrentStage.ToString & _
                            " - " & StagesGrid.Item(StagesGrid.Columns("Name").Index, StagesGrid.CurrentRow.Index).Value.ToString
            StageForm.StageHeader = StageForm.Text

        End With


        If ItsMine Then
            StageForm.AddBut.Enabled = True
            StageForm.DeleteBut.Enabled = True
            StageForm.SaveButton.Enabled = True
            StageForm.Button1.Enabled = True
            StageForm.DescriptionBox.Enabled = True
        Else
            StageForm.AddBut.Enabled = False
            StageForm.DeleteBut.Enabled = False
            StageForm.SaveButton.Enabled = False
            StageForm.Button1.Enabled = False
            StageForm.DescriptionBox.Enabled = False

        End If
        

        StageForm.DescriptionBox.Text = StagesGrid.Item(StagesGrid.Columns("Name").Index, StagesGrid.CurrentRow.Index).Value.ToString
        'Stage Form Docking to Main Form
        StageForm.Who = "Stage"
        StageForm.WarningLabel.Text = "Please enter a Stage Name"
        StageForm.MdiParent = MainForm
        StageForm.Dock = DockStyle.Fill
        StageForm.Show()
    End Sub

    

    Private Sub EditProduct()
        If ProductGrid.RowCount = 0 Then Exit Sub

        If IsNothing(ProductView.SelectedNode) Then Exit Sub

        If ProductView.SelectedNode.Name.StartsWith("NF") Then Exit Sub


        StageForm.CurrentStage = ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value


        StageForm.Text = "Product - " & ProductGrid.Item(ProductGrid.Columns("Name").Index, ProductGrid.CurrentRow.Index).Value.ToString
        StageForm.StageHeader = StageForm.Text
       

        StageForm.DescriptionBox.Text = ProductGrid.Item(ProductGrid.Columns("Name").Index, ProductGrid.CurrentRow.Index).Value.ToString

        If ItsMine Then
            StageForm.AddBut.Enabled = True
            StageForm.DeleteBut.Enabled = True
            StageForm.SaveButton.Enabled = True
            StageForm.Button1.Enabled = True
            StageForm.DescriptionBox.Enabled = True
        Else
            StageForm.AddBut.Enabled = False
            StageForm.DeleteBut.Enabled = False
            StageForm.SaveButton.Enabled = False
            StageForm.Button1.Enabled = False
            StageForm.DescriptionBox.Enabled = False
        End If

        'Stage Form Docking to Main Form
        StageForm.Who = "Product"
        StageForm.WarningLabel.Text = "Please enter a Product Name"
        StageForm.MdiParent = MainForm
        StageForm.Dock = DockStyle.Fill
        StageForm.Show()
    End Sub

    Private Sub EditBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditBut.Click
        EditStage()

    End Sub

    Private Sub ModelForm_Activated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Activated
        LoadStages()
        LoadProducts()

    End Sub

    Private Sub StagesGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles StagesGrid.CellEnter
        'only allow the last line to be deleted
        If StagesGrid.RowCount = 0 Then Exit Sub

        If StagesGrid.CurrentRow.Index = StagesGrid.RowCount - 1 Then
            If ItsMine Then
                DeleteBut.Enabled = True
            Else
                DeleteBut.Enabled = False
            End If

        Else
            DeleteBut.Enabled = False

        End If
    End Sub

    Private Sub DeleteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteBut.Click
        Dim sql As String

        If StagesGrid.RowCount = 0 Then Exit Sub


        If MessageBox.Show("Are you sure you want to delete Stage " & _
                            StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, StagesGrid.CurrentRow.Index).Value.ToString & "?", _
                           "Delete Stage?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

            'first delete any steps attached to this stage
            sql = "delete from Modelling_Steps " & _
                  "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and " & _
                  "Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' and " & _
                  "Stage_Number = " & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, StagesGrid.CurrentRow.Index).Value.ToString
            RunNonQuery(sql)

            sql = "delete from Modelling_Stages " & _
                  "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and " & _
                  "Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' and " & _
                  "Stage_Number = " & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, StagesGrid.CurrentRow.Index).Value.ToString
            RunNonQuery(sql)

            LoadStages()



        End If

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddProdBut.Click
        AddProduct()

    End Sub

   
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductEditBut.Click
        EditProduct()

    End Sub

    Private Sub ProdDelBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProdDelBut.Click
        Dim sql As String
        Dim TheView As DataView

        If ProductGrid.RowCount = 0 Then Exit Sub


        If MessageBox.Show("Are you sure you want to delete Product " & _
                            ProductGrid.Item(ProductGrid.Columns("Name").Index, ProductGrid.CurrentRow.Index).Value.ToString & "?", _
                           "Delete Product?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

            'make sure that this product is not used in any stages or other products
            
            sql = "Select * from Modelling_Steps_Products where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' " & _
                  " and (DataCut_ID = '" & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString & "'" & _
                  " or DataCut_ID_Exception = '" & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString & "'" & _
                  " or (DataCut_ID_Condition = '" & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString & "' and Condition <> 'no'))"

            TheView = LoadDataView(sql)

            If TheView.Count > 0 Then
                TheView = Nothing

                MessageBox.Show("This product is used in at least one other product.  Remove it from all products first.", "Cannot delete product", MessageBoxButtons.OK, MessageBoxIcon.Error)

                Exit Sub

            End If

            sql = "Select * from Modelling_Steps where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' " & _
                  " and (DataCut_ID = '" & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString & "'" & _
                  " or DataCut_ID_Exception = '" & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString & "'" & _
                  " or (DataCut_ID_Condition = '" & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString & "' and Condition <> 'no'))"

            TheView = LoadDataView(sql)

            If TheView.Count > 0 Then
                TheView = Nothing

                MessageBox.Show("This product is used in at least one other stage.  Remove it from all stages first.", "Cannot delete product", MessageBoxButtons.OK, MessageBoxIcon.Error)

                Exit Sub

            End If

           

            'first delete any steps attached to this product
            sql = "delete from Modelling_Steps_Products " & _
                  "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and " & _
                  "Product_Number = " & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString
            RunNonQuery(sql)

            sql = "delete from Modelling_Products " & _
                  "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and " & _
                  "Product_Number = " & ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, ProductGrid.CurrentRow.Index).Value.ToString
            RunNonQuery(sql)

            LoadProducts()


        End If
    End Sub

    Public Function ScenarioResultsSQL() As String
        Job("Loading Scenario Results...")
        Dim r As Integer
        Dim sql As String
        Dim SelectPart, FromPart As String
        Dim StageSQL As String
        Dim GrandTotal As String
        Dim Learners As String

        sql = ""
        SelectPart = "Select R.RID, R.UPIN, R.Provider_Name as [Provider Name], R.LA_Code as [Local Authority Code], R.LA_Name as [Local Authority], R.Region_Code as Region "
        FromPart = " From All_Provider_Fields R "
        GrandTotal = ""
        Learners = ""

        If AllocationLinesGrid.Rows.Count = 0 Then
            ScenarioResultsSQL = ""
            Exit Function
        End If

        For r = 0 To AllocationLinesGrid.Rows.Count - 1
            AllocationLinesGrid.CurrentCell = AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, r)
            'hopefully this should trigger the load of the stages grid

            StageSQL = FundingStreamResults()

            If StageSQL <> "" Then
                sql = sql & " left outer join ( " & StageSQL & ") AL" & r
                sql = sql & " on (R.RID = AL" & r & ".RID) "

                SelectPart = SelectPart & ", AL" & r & ".[Grand Total] as [" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, r).Value.ToString & "] "
                SelectPart = SelectPart & ", AL" & r & ".[Total Learners] as [" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, r).Value.ToString & " Learners] "

                If GrandTotal = "" Then
                    GrandTotal = "coalesce(AL" & r & ".[Grand Total],0) "
                Else
                    GrandTotal = GrandTotal & " + " & "coalesce(AL" & r & ".[Grand Total],0) "
                End If

                If Learners = "" Then
                    Learners = "coalesce(AL" & r & ".[Total Learners],0) "
                Else
                    Learners = Learners & " + " & "coalesce(AL" & r & ".[Total Learners],0) "
                End If
            End If
        Next

        GrandTotal = GrandTotal & " as [Grand Total] "
        Learners = Learners & " as [Total Learners] "

        ScenarioResultsSQL = SelectPart & ", " & GrandTotal & ", " & Learners & " " & FromPart & sql & " where R.Lead = 1"

        Clipboard.Clear()
        Clipboard.SetText(ScenarioResultsSQL)

    End Function

    Public Function FundingStreamResults() As String

        If StagesGrid.RowCount = 0 Then
            FundingStreamResults = ""
            Exit Function
        End If

        Dim sql As String
        Dim r As Integer
        Dim SelectPart, FromPart As String
        Dim GrandTotal As String
        Dim Learners As String

        sql = ""
        GrandTotal = ""
        Learners = ""
        SelectPart = "Select R.RID, R.UPIN, R.Provider_Name as [Provider Name], R.LA_Code as [Local Authority Code], R.LA_Name as [Local Authority], R.Region_Code as Region "
        FromPart = "From All_Provider_Fields R "

        With Modelling_ResultsForm
            'go through each stage to build the sql
            For r = 0 To StagesGrid.RowCount - 1
                sql = sql & "left outer join ( "

                sql = sql & .StageSQL(ScenarioBox.SelectedValue.ToString, AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString _
                                               , StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value, "Stage", True)

                sql = sql & " ) Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & " "

                sql = sql & "on (R.RID = Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & ".RID) "

                SelectPart = SelectPart & ", coalesce(Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & ".[Final Result],0) as [" & _
                                          "Stage " & StagesGrid.Item(StagesGrid.Columns("Name").Index, r).Value.ToString & " Result] "

                SelectPart = SelectPart & ", coalesce(Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & ".Learners,0) as [" & _
                                          "Stage " & StagesGrid.Item(StagesGrid.Columns("Name").Index, r).Value.ToString & " Learners] "

                If GrandTotal = "" Then
                    GrandTotal = "coalesce(Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & ".[Final Result],0) "
                Else
                    GrandTotal = GrandTotal & " + " & "coalesce(Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & ".[Final Result],0) "
                End If

                If Learners = "" Then
                    Learners = "coalesce(Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & ".Learners,0) "
                Else
                    Learners = Learners & " + " & "coalesce(Stage" & StagesGrid.Item(StagesGrid.Columns("Stage_Number").Index, r).Value.ToString & ".Learners,0) "
                End If
            Next

            GrandTotal = GrandTotal & " as [Grand Total] "
            Learners = "round(" & Learners & ",0) as [Total Learners] "

            sql = SelectPart & ", " & GrandTotal & ", " & Learners & FromPart & sql

            'select only the providers that this funding stream relates too

            sql = sql & " where R.UPIN in (select UPIN from Provider_Funding_Streams where Period_ID = " & PeriodBox.SelectedValue & " and Budget_ID = '" & _
                          AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Budget_ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "')"
            sql = sql & " and R.Lead = 1 "
        End With
        FundingStreamResults = sql

    End Function

    Private Sub ResultsBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ResultsBut.Click
        If StagesGrid.RowCount = 0 Then Exit Sub


        ShowLoadingForm()
        Job("Loading Results...")

        Dim sql As String

        sql = FundingStreamResults()

        With Modelling_ResultsForm
            .ResultsGrid.AutoGenerateColumns = True
            .ResultsGrid.Columns.Clear()
            .TheBS.DataSource = LoadDataTable(sql)

            .Text = Trim(ScenarioBox.Text) & " - " & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & " Results"

            .Show()
            .Focus()

            HideLoadingForm()
            Job("Ready")
        End With
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        LoadScenarioBox()
        SetupCurrentScenario()


    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteScenarioBut.Click
        If ScenarioBox.SelectedIndex = -1 Then Exit Sub

        If Not ItsMine Then Exit Sub

        If MessageBox.Show("Are you sure you want to delete this scenario?  You cannot undo this!", "Delete?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
            Dim sql As String

            sql = "delete from Modelling_Steps where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"
            RunNonQuery(sql)

            sql = "delete from Modelling_Steps_Products where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"
            RunNonQuery(sql)

            sql = "delete from Modelling_Stages where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"
            RunNonQuery(sql)

            sql = "delete from Modelling_Scenarios where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"
            RunNonQuery(sql)

            sql = "delete from Modelling_Products where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"
            RunNonQuery(sql)

            sql = "delete from Modelling_Product_Folders where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"
            RunNonQuery(sql)

            LoadScenarioBox()
            SetupCurrentScenario()
            LoadProducts()
            LoadProductsView()

            If ScenarioBox.Items.Count = 0 Then
                SetToolButtons(False)
            Else
                SetToolButtons(True)
            End If

            AllocationLinesGrid.CurrentCell = AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, 0)

            MessageBox.Show("The scenario has been delete", "Delete", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Lets copy the scenario
        Dim ScenarioName As String
        Dim TheID As String

        If ScenarioBox.Items.Count = 0 Then Exit Sub

        ScenarioName = InputBox("Please enter a name for the new scenario", "Copy Scenario")

        If ScenarioName <> "" Then
            Dim sql As String
            TheID = Format(Now, "ddMMyyhhmmssff")
            sql = "insert into Modelling_Scenarios (Scenario_ID, Scenario_Description, User_Name, Published) values('" & TheID & "', '" & ScenarioName & "', '" & CurrentUser.Name & "', 0)"

            If RunNonQuery(sql) = "" Then

                'Products
                sql = "insert into Modelling_Products " & _
                      "select '" & TheID & "', Product_Number, Product_Description, Folder_ID from Modelling_Products " & _
                      "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

                RunNonQuery(sql)

                'Modelling_Stages
                sql = "insert into Modelling_Stages " & _
                      "select '" & TheID & "', Allocation_ID, Stage_Number, Stage_Description from Modelling_Stages " & _
                      "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

                RunNonQuery(sql)

                'Modelling_Steps
                sql = "insert into Modelling_Steps " & _
                      "select '" & TheID & "', Allocation_ID, Stage_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value from Modelling_Steps " & _
                      "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

                RunNonQuery(sql)

                'Modelling_Steps_Products
                sql = "insert into Modelling_Steps_Products " & _
                      "select '" & TheID & "', Product_Number, Step_Number, Operator, Table_Name, DataCut_ID, DataCut_ID_Exception, Condition, Table_Name_Condition, DataCut_ID_Condition, Constant, Constant_Exception, Learner_Flag, Table_Name_Exception, Table_Name_Condition_Value, DataCut_ID_Condition_Value from Modelling_Steps_Products " & _
                      "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

                RunNonQuery(sql)

                'Folder Structure
                sql = "insert into Modelling_Product_Folders " & _
                      "select '" & TheID & "', Folder_ID, Folder_Description, Parent_ID, TheLevel from Modelling_Product_Folders " & _
                      "where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"
                RunNonQuery(sql)

                MessageBox.Show("The scenario was successfuly created and copied", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)


                ViewbyBox.SelectedValue = "<My>"
                LoadScenarioBox()
                ScenarioBox.SelectedValue = TheID

                AllocationLinesGrid.Refresh()


            Else
                MessageBox.Show("There was an error creating the scenario", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End If
        End If
    End Sub

    Private Sub PublishBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PublishBut.Click
        If Not ItsMine Then Exit Sub

        If PublishBut.Text = "Publish Scenario" Then
            If MessageBox.Show("By clicking yes this Scenario will be viewable by all authorised users", "Publish Scenario?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                Dim sql As String

                sql = "update Modelling_Scenarios " & _
                      "set Published = 1 where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

                RunNonQuery(sql)

                SetupCurrentScenario()

                MessageBox.Show("The scenario has now been published", "Published", MessageBoxButtons.OK)

            End If
        Else
            If MessageBox.Show("By clicking yes the publish status of this scenario will be removed", "Remove publish status?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                Dim sql As String

                sql = "update Modelling_Scenarios " & _
                      "set Published = 0 where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "'"

                RunNonQuery(sql)

                SetupCurrentScenario()

                MessageBox.Show("The publish status has now bee removed", "Un-published", MessageBoxButtons.OK)

            End If
        End If

    End Sub

    Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        If MessageBox.Show("Please be aware that depending on the size and complexity of your scenario, this could take a few minutes.  Please wait for the task to finish.  Would you like to continue?", "This could take some time", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
            Exit Sub

        End If

        'ShowLoadingForm()
        'Job("Loading Results...")

        'Dim sql As String

        'sql = "select s.*, st.Stage_Description, al.Budget_ID  from dbo.Modelling_Steps s, dbo.Allocation_Lines al, dbo.Modelling_Stages st where s.Allocation_ID = al.Allocation_ID " & _
        '    " and s.Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' " & _
        '    " and st.Scenario_ID = s.Scenario_ID and st.Allocation_ID = s.Allocation_ID and st.Stage_Number = s.Stage_Number and al.Hidden = 0 " & _
        '    " order by s.Scenario_ID, al.Budget_ID, s.Allocation_ID, s.Stage_Number, s.Step_Number"

        'sql = Modelling_ResultsForm.ScenarioSQLv3(sql, 4, "", True)
        'With Modelling_ResultsForm
        '    .ResultsGrid.AutoGenerateColumns = True
        '    .ResultsGrid.Columns.Clear()
        '    .TheBS.DataSource = LoadDataTable(sql)

        '    .Text = Trim(ScenarioBox.Text) & " - " & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & " Results"

        '    .Show()
        '    .Focus()


        'End With

        'HideLoadingForm()
        'Job("Ready")

        Dim Sql As String

        Sql = "select * from dbo.Modelling_Stages where scenario_id = '" & ScenarioBox.SelectedValue.ToString & "'" & _
              " order by Allocation_ID, Stage_Number"

        With Modelling_Process
            .StagesSQL = Sql
            .IsStage = True
            .IsAllocationLine = True
            .TheRID = ""
            .CanClearTempData = True
            .ReturnSQL = False
            .Process_Period_ID = PeriodBox.SelectedValue
            .ShowDialog()
        End With

     
    End Sub

    Private Sub CasesButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CasesButton.Click
        CreatedCasesAdminForm.Icon = MainForm.Icon
        CreatedCasesAdminForm.ShowDialog()


    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReportDesignerBut.Click
        'Reportdesignerform  Docking to Main Form
        ReportDesignerForm.MdiParent = MainForm
        ReportDesignerForm.Dock = DockStyle.Fill
        ReportDesignerForm.Show()

    End Sub

    Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim sql As String

        'ShowLoadingForm()
        'Job("Loading Results...")

        'sql = "select s.*, st.Stage_Description, al.Budget_ID  from dbo.Modelling_Steps s, dbo.Allocation_Lines al, dbo.Modelling_Stages st where s.Allocation_ID = al.Allocation_ID " & _
        '    " and s.Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and s.Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
        '    " and st.Scenario_ID = s.Scenario_ID and st.Allocation_ID = s.Allocation_ID and st.Stage_Number = s.Stage_Number " & _
        '    " order by s.Scenario_ID, al.Budget_ID, s.Allocation_ID, s.Stage_Number, s.Step_Number"

        'sql = Modelling_ResultsForm.ScenarioSQLv3(sql, 2, AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Budget_ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString, True)
        'With Modelling_ResultsForm
        '    .ResultsGrid.AutoGenerateColumns = True
        '    .ResultsGrid.Columns.Clear()
        '    .TheBS.DataSource = LoadDataTable(sql)

        '    .Text = Trim(ScenarioBox.Text) & " - " & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & " Results"

        '    .Show()
        '    .Focus()


        'End With

        'HideLoadingForm()
        'Job("Ready")

        sql = "select * from dbo.Modelling_Stages where scenario_id = '" & ScenarioBox.SelectedValue.ToString & "'" & _
                " and Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
                " order by Allocation_ID, Stage_Number"

        With Modelling_Process
            .StagesSQL = sql
            .IsStage = True
            .IsAllocationLine = True
            .TheRID = ""
            .CanClearTempData = True
            .ReturnSQL = False
            .Session_ID_Main = ""
            .Process_Period_ID = PeriodBox.SelectedValue
            .Text = AllocationLinesGrid.CurrentRow.Cells("Description").Value
            .ShowDialog()
        End With

    End Sub

    Private Sub ToolStripButton1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        If CurrentUser.Level < 5 Then
            CreateDataCutsToolStripMenuItem.Enabled = False
        Else
            CreateDataCutsToolStripMenuItem.Enabled = True
        End If

    End Sub

    Private Sub ProductView_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles ProductView.AfterSelect
        'select the one in the grid
        Dim r As Integer
        Dim selected As Integer = -1

        If ProductGrid.RowCount <= 0 Then Exit Sub

        If ProductView.SelectedNode.Name.StartsWith("NF") Then

            Exit Sub
        End If

        For r = 0 To ProductGrid.RowCount - 1
            If ProductGrid.Item(ProductGrid.Columns("Product_Number").Index, r).Value = ProductView.SelectedNode.Name.ToString Then
                selected = r


            End If
        Next

        If selected <> -1 Then
            ProductGrid.CurrentCell = ProductGrid.Item(ProductGrid.Columns("Name").Index, selected)
            CurrentProduct = ProductGrid.Item(ProductGrid.Columns("Product_number").Index, selected).Value
        End If
    End Sub

    Private Sub Button6_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddFolderBut.Click
        'add a folder
        Dim FolderID As String

        FolderID = "NF" & Format(Now, "ddMMyyhhmmssff")

        ProductView.Nodes.Add(FolderID, "New Folder", 2, 2)

        Dim sql As String

        sql = "insert into Modelling_Product_Folders (Scenario_ID, Folder_ID, Folder_Description, Parent_ID, TheLevel) values('" & ScenarioBox.SelectedValue.ToString & "', '" & FolderID & "', 'New Folder', null, 0)"

        RunNonQuery(sql)

    End Sub

    Private Sub ProductView_ItemDrag(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ItemDragEventArgs) Handles ProductView.ItemDrag
        ' Set the drag node and initiate the DragDrop 
        DoDragDrop(e.Item, DragDropEffects.Move)
    End Sub

    Private Sub ProductView_DragEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles ProductView.DragEnter
        'See if there is a TreeNode being dragged
        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
            True) Then
            'TreeNode found allow move effect
            e.Effect = DragDropEffects.Move
        Else
            'No TreeNode found, prevent move
            e.Effect = DragDropEffects.None
        End If
    End Sub

    Private Sub ProductView_DragOver(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles ProductView.DragOver
        'Check that there is a TreeNode being dragged 
        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
               True) = False Then Exit Sub

        'Get the TreeView raising the event (incase multiple on form)
        Dim selectedTreeview As TreeView = CType(sender, TreeView)

        'As the mouse moves over nodes, provide feedback to 
        'the user by highlighting the node that is the 
        'current drop target
        Dim pt As Point = _
            CType(sender, TreeView).PointToClient(New Point(e.X, e.Y))
        Dim targetNode As TreeNode = selectedTreeview.GetNodeAt(pt)

        'See if the targetNode is currently selected, 
        'if so no need to validate again
        ' If Not (selectedTreeview.SelectedNode Is targetNode) Then
        'Select the    node currently under the cursor
        selectedTreeview.SelectedNode = targetNode

        'Check that the selected node is not the dropNode and
        'also that it is not a child of the dropNode and 
        'therefore an invalid target
        Dim dropNode As TreeNode = _
            CType(e.Data.GetData("System.Windows.Forms.TreeNode"),  _
            TreeNode)

        If Not IsNothing(targetNode) Then
            If Not targetNode.Name.StartsWith("NF") Then
                e.Effect = DragDropEffects.None
                Exit Sub
            End If
        End If

        If dropNode.Name = "NF" Then
            'cant move the root node
            e.Effect = DragDropEffects.None
            Exit Sub
        End If

        'am I moving a folder? if so make sure you dont move it to the root node NF
        If dropNode.Name.StartsWith("NF") Then
            If Not IsNothing(targetNode) Then
                If targetNode.Name = "NF" Then
                    e.Effect = DragDropEffects.None
                    Exit Sub
                End If
            End If
        End If

        Do Until targetNode Is Nothing
            If targetNode Is dropNode Then
                e.Effect = DragDropEffects.None
                Exit Sub
            End If
            targetNode = targetNode.Parent
        Loop
        '   End If

        'Currently selected node is a suitable target
        e.Effect = DragDropEffects.Move
    End Sub

    Private Sub ProductView_DragDrop(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles ProductView.DragDrop
        'Check that there is a TreeNode being dragged
        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
              True) = False Then Exit Sub

        'Get the TreeView raising the event (incase multiple on form)
        Dim selectedTreeview As TreeView = CType(sender, TreeView)

        'Get the TreeNode being dragged
        Dim dropNode As TreeNode = _
              CType(e.Data.GetData("System.Windows.Forms.TreeNode"),  _
              TreeNode)

        'The target node should be selected from the DragOver event
        Dim targetNode As TreeNode = selectedTreeview.SelectedNode

        'Remove the drop node from its current location
        dropNode.Remove()

        'If there is no targetNode add dropNode to the bottom of
        'the TreeView root nodes, otherwise add it to the end of
        'the dropNode child nodes
        If targetNode Is Nothing Then
            selectedTreeview.Nodes.Add(dropNode)
        Else
            targetNode.Nodes.Add(dropNode)
        End If

        'Ensure the newley created node is visible to
        'the user and select it
        dropNode.EnsureVisible()
        selectedTreeview.SelectedNode = dropNode

        'now save it
        Dim sql As String

        If dropNode.Name.Contains("NF") Then
            'folders has been moved
            'does it have a parent
            If IsNothing(dropNode.Parent) Then
                sql = "update Modelling_Product_Folders set Parent_ID = null, TheLevel = " & dropNode.Level & " where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Folder_ID = '" & ProductView.SelectedNode.Name & "'"
            Else
                sql = "update Modelling_Product_Folders set Parent_ID = '" & dropNode.Parent.Name & "', TheLevel = " & dropNode.Level & " where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Folder_ID = '" & ProductView.SelectedNode.Name & "'"
            End If


            RunNonQuery(Sql)
        Else
            'product has moved
            If IsNothing(dropNode.Parent) Then
                sql = "update Modelling_Products set Folder_ID = null where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Product_Number = '" & ProductView.SelectedNode.Name & "'"
            Else
                If dropNode.Parent.Name = "NF" Then
                    sql = "update Modelling_Products set Folder_ID = null where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Product_Number = '" & ProductView.SelectedNode.Name & "'"
                Else
                    sql = "update Modelling_Products set Folder_ID = '" & dropNode.Parent.Name & "' where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Product_Number = '" & ProductView.SelectedNode.Name & "'"
                End If
            End If

            RunNonQuery(Sql)

        End If
    End Sub

    Private Sub ProductView_AfterLabelEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.NodeLabelEditEventArgs) Handles ProductView.AfterLabelEdit
        Dim sql As String

        If Trim(e.Label) = "" Then Exit Sub

        If ScenarioBox.Items.Count = 0 Then Exit Sub

        If ProductView.SelectedNode.Name.StartsWith("NF") Then
            'folder name change
            sql = "update Modelling_Product_Folders set Folder_Description = '" & e.Label & "' where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Folder_ID = '" & ProductView.SelectedNode.Name & "'"
            RunNonQuery(sql)
        Else
            'product name changed
            sql = "update Modelling_Products set Product_Description = '" & e.Label & "' where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Product_Number = '" & ProductView.SelectedNode.Name & "'"
            RunNonQuery(sql)
            ProductGrid.Item(ProductGrid.Columns("Name").Index, ProductGrid.CurrentRow.Index).Value = e.Label

        End If


    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteFolderBut.Click
        If IsNothing(ProductView.SelectedNode) Then Exit Sub

        If Not ProductView.SelectedNode.Name.StartsWith("NF") Then Exit Sub

        'now check to see if there are any children
        If ProductView.SelectedNode.Nodes.Count <> 0 Then
            MessageBox.Show("Please delete all products and folders within this folder before attempting to delete it.", "Folder not empty", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If MessageBox.Show("Are you sure you want to delete the selected folder?", "Delete folder?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            RunNonQuery("delete from Modelling_Product_Folders where Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Folder_ID = '" & ProductView.SelectedNode.Name & "'")
            LoadProductsView()

        End If
    End Sub

    Private Sub ToolStripButton1_ButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.ButtonClick
        If MessageBox.Show("Please be aware that depending on the size and complexity of your scenario, this could take a few minutes.  Please wait for the task to finish.  Would you like to continue?", "This could take some time", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
            Exit Sub

        End If

        'ShowLoadingForm()
        'Job("Loading Results...")

        'Dim sql As String

        'sql = "select s.*, st.Stage_Description, al.Budget_ID  from dbo.Modelling_Steps s, dbo.Allocation_Lines al, dbo.Modelling_Stages st where s.Allocation_ID = al.Allocation_ID " & _
        '    " and s.Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Budget_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Budget_ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
        '    " and st.Scenario_ID = s.Scenario_ID and st.Allocation_ID = s.Allocation_ID and st.Stage_Number = s.Stage_Number and al.Hidden = 0 " & _
        '    " order by s.Scenario_ID, al.Budget_ID, s.Allocation_ID, s.Stage_Number, s.Step_Number"

        'sql = Modelling_ResultsForm.ScenarioSQLv3(sql, 3, AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Budget_ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString, True)
        'With Modelling_ResultsForm
        '    .ResultsGrid.AutoGenerateColumns = True
        '    .ResultsGrid.Columns.Clear()
        '    .TheBS.DataSource = LoadDataTable(sql)

        '    .Text = Trim(ScenarioBox.Text) & " - " & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Description").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & " Results"

        '    .Show()
        '    .Focus()


        'End With

        'HideLoadingForm()
        'Job("Ready")
        Dim Sql As String

        Sql = "select * from dbo.Modelling_Stages where scenario_id = '" & ScenarioBox.SelectedValue.ToString & "'" & _
               " and Allocation_ID in (select Allocation_ID from Allocation_Lines where Budget_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Budget_ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "')" & _
               " order by Allocation_ID, Stage_Number"

        With Modelling_Process
            .StagesSQL = Sql
            .IsStage = True
            .IsAllocationLine = True
            .Session_ID_Main = ""
            .Process_Period_ID = PeriodBox.SelectedValue
            .ShowDialog()
        End With

    End Sub

    Private Sub CreateDataCutsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateDataCutsToolStripMenuItem.Click
        'Start the Datacut Creator

        If DataCutCreatorForm.ShowDialog = Windows.Forms.DialogResult.OK Then
            CreateDataCut(DataCutCreatorForm.TheView, DataCutCreatorForm.DataCutBox.SelectedValue.ToString, DataCutCreatorForm.TypeBox.Text)
        End If
    End Sub

    Private Sub CreateDataCut(ByVal TheView As TreeView, ByVal DataCut_ID As String, ByVal TheTable As String)
        Dim TheName As String
        Dim TheData As DataView
        Dim sql As String

        'Sort out the name
        If DataCut_ID = "<New>" Then
            TheName = InputBox("Please enter a name for this Data Cut", "Data Cut Name", "")
            If TheName = "" Then
                MessageBox.Show("Procedure cancelled by user", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            DataCut_ID = "DCC" & Format(Now, "ddMMyyhhmmssff")
        Else
            'find it in the db
            TheData = LoadDataView("Select top 1 Description from Modelling_" & TheTable & " where DataCut_ID = '" & DataCut_ID & "'")

            If TheData.Count = 0 Then
                TheName = InputBox("Please enter a name for this Data Cut", "Data Cut Name", "")
                If TheName = "" Then
                    MessageBox.Show("Procedure cancelled by user", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                End If
            Else
                TheName = TheData(0).Item("Description").ToString
            End If

            'just delete all data from the datacut first
            sql = "delete from Modelling_" & TheTable & " where DataCut_ID = '" & DataCut_ID & "'"
            RunNonQuery(sql)
        End If

        While Len(TheName) > 50
            MessageBox.Show("The name you entered is too long.  It cannot be more than 50 characters.  Please try again", "Name too long", MessageBoxButtons.OK, MessageBoxIcon.Error)
            TheName = InputBox("Please enter a name for this Data Cut", "Data Cut Name", TheName)
        End While

        If TheName = "" Then Exit Sub

        If MessageBox.Show("Please be aware that depending on the size and complexity of your scenario, this could take a few minutes.  Please wait for the task to finish.  Would you like to continue?", "This could take some time", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
            Exit Sub

        End If

        ' ShowLoadingForm()
        Job("Loading Stages...")


        'work out which stages to load
        Dim tn As TreeNode
        Dim tns As TreeNode
        Dim tsql As String = ""

        For Each tn In TheView.Nodes
            For Each tns In tn.Nodes
                If tns.Checked Then
                    If tsql <> "" Then
                        tsql = tsql & " or "
                    End If

                    tsql = tsql & "(Allocation_ID = '" & tn.Name & "' and Stage_Number = " & tns.Name & ")"
                End If
            Next
        Next



        sql = "select * from ("

        sql = sql & "select s.*, st.Stage_Description, al.Budget_ID  from dbo.Modelling_Steps s, dbo.Allocation_Lines al, dbo.Modelling_Stages st where s.Allocation_ID = al.Allocation_ID " & _
            " and s.Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and Budget_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Budget_ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
            " and st.Scenario_ID = s.Scenario_ID and st.Allocation_ID = s.Allocation_ID and st.Stage_Number = s.Stage_Number) a " & _
            " where " & tsql & _
            " order by Scenario_ID, Budget_ID, Allocation_ID, Stage_Number, Step_Number"

        'sql = Modelling_ResultsForm.ScenarioSQLv3(sql, 3, AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Budget_ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString, False)


        With Modelling_Process
            .StagesSQL = sql
            .IsStage = True
            .IsAllocationLine = True
            .TheRID = ""
            .CanClearTempData = True
            .ReturnSQL = True
            .Session_ID_Main = ""
            .Process_Period_ID = PeriodBox.SelectedValue
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                sql = .SQLTextBox.Text
            Else
                Try
                    .Close()
                Catch ex As Exception

                End Try
                HideLoadingForm()
                Job("Ready")
                Exit Sub
            End If
        End With

        TheData = LoadDataView(sql)

        Job("Inserting data into data cut...")
        If TheData.Count <> 0 Then
            Dim rec As DataRowView
            Dim TheValue As String

            'insert it into the datacut table
            With ProgressBarForm.TheProgressBar
                .Minimum = 0
                .Maximum = TheData.Count
                .Step = 1
            End With

            ProgressBarForm.Show()

            For Each rec In TheData
                ProgressBarForm.TheProgressBar.PerformStep()
                If TheTable = "Learners" Then
                    TheValue = Math.Round(rec.Item("Learners"), 0).ToString
                Else
                    TheValue = rec.Item("Final Result").ToString
                End If

                If TheValue.Length <> 0 Then
                    sql = "insert into Modelling_" & TheTable & " values (" & _
                    "'" & DataCut_ID & "', '" & rec.Item("RID").ToString & "', 'DCC', 'Approved', '" & TheName & "'," & TheValue & ")"
                    RunNonQuery(sql)
                End If

            Next

            ProgressBarForm.Close()

        End If

        HideLoadingForm()

        Job("Ready")

        MessageBox.Show("The data cut has been created", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub ApplyToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub ApplyAllocation(ByVal Budget_ID As String, ByVal Allocation_ID As String, ByVal PeriodID As Integer, ByVal Factor_Datacut As String, ByVal CaseFactor_DataCut As String, ByVal Rate As Double, ByVal TheNotes As String)
        Dim Sql As String
        Dim TheData As DataView
        '   Dim Factor As DataView
        '   Dim ACheck As DataView
        Dim FinalResult As Decimal = 0
        Dim Learners As Integer = 0
        '  Dim CurrentAllocation As Decimal
        '   Dim CurrentLearners As Integer
        '  Dim CurrentFactor As Decimal
        '   Dim CurrentRate As Decimal

        '  Dim TheDate As Double

        'check to see if a pattern exists for this allocation id

        Sql = "select * from Pattern_IDs where Allocation_ID = '" & Allocation_ID & "' and Period_ID = " & PeriodID

        TheData = LoadDataView(Sql)

        If TheData.Count = 0 Then
            MessageBox.Show("No national pattern exists for this Allocation ID and Period, please create the national pattern before attempting to apply the allocation", "No National Pattern", MessageBoxButtons.OK)
            Exit Sub
        End If

        '    ShowLoadingForm()

        'first set all allocations in this allocation ID to Edit

        'Sql = "update Allocations set Status = 'Edit' where Allocation_ID = '" & Allocation_ID & "' and Status = 'Approved' and Period_ID = " & PeriodID

        'Job("Setting Allocation to Edit...")
        'RunNonQuery(Sql)

        'Sql = "select s.*, st.Stage_Description, al.Budget_ID  from dbo.Modelling_Steps s, dbo.Allocation_Lines al, dbo.Modelling_Stages st where s.Allocation_ID = al.Allocation_ID " & _
        '    " and s.Scenario_ID = '" & ScenarioBox.SelectedValue.ToString & "' and s.Allocation_ID = '" & Allocation_ID & "'" & _
        '    " and st.Scenario_ID = s.Scenario_ID and st.Allocation_ID = s.Allocation_ID and st.Stage_Number = s.Stage_Number " & _
        '    " order by s.Scenario_ID, al.Budget_ID, s.Allocation_ID, s.Stage_Number, s.Step_Number"

        'Sql = Modelling_ResultsForm.ScenarioSQLv3(Sql, 2, Budget_ID, False)


        Sql = "select * from dbo.Modelling_Stages where scenario_id = '" & ScenarioBox.SelectedValue.ToString & "'" & _
               " and Allocation_ID = '" & Allocation_ID & "'" & _
               " order by Allocation_ID, Stage_Number"

        With Modelling_Process
            .StagesSQL = Sql
            .IsStage = True
            .IsAllocationLine = True
            .TheRID = ""
            .CanClearTempData = True
            .ReturnSQL = True
            .Session_ID_Main = ""
            .Process_Period_ID = PeriodID
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Sql = .SQLTextBox.Text

            Else
                Try
                    .Close()
                Catch ex As Exception

                End Try
                '   HideLoadingForm()
                Job("Ready")
                Exit Sub
            End If
        End With

        '  TheData = LoadDataView(Sql)

        ' TheDate = Now.ToOADate - 2

        Job("Applying the Allocation...")

        Dim p As New List(Of Object)
        p.Clear()

        p.Add(Sql)
        p.Add(CaseFactor_DataCut)
        p.Add(Factor_Datacut)
        p.Add(PeriodID)
        p.Add(Allocation_ID)
        p.Add(Rate)
        p.Add(TheNotes)

        If DoSomeWork("UpdateLRAllocations", p, False) = Windows.Forms.DialogResult.OK Then
            MessageBox.Show("The Allocation has been applied", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        'If TheData.Count <> 0 Then
        '    Dim rec As DataRowView

        '    'insert it into the allocations table
        '    With ProgressBarForm.TheProgressBar
        '        .Minimum = 0
        '        .Maximum = TheData.Count
        '        .Step = 1
        '    End With

        '    ProgressBarForm.Text = Modelling_ResultsForm.GetAllocation(Allocation_ID)
        '    ProgressBarForm.Show()

        '    Dim ProviderFactor As String
        '    Dim TheFactor As Decimal

        '    For Each rec In TheData
        '        ProgressBarForm.TheProgressBar.PerformStep()
        '        'get the provider factor
        '        If CaseFactor_DataCut = "none" Then
        '            Sql = "select RID, coalesce(Value,0) as Value from dbo.Modelling_Factors " & _
        '                "where datacut_id = '" & Factor_Datacut & "' " & _
        '                "and RID = '" & rec.Item("RID").ToString & "' and Status = 'Approved'"
        '        Else
        '            Sql = "select act.RID, coalesce(coalesce(c.Value,act.Value),0) as Value from " & _
        '            "(select RID, Value from dbo.Modelling_Factors " & _
        '            "where datacut_id = '" & Factor_Datacut & "' and Status = 'Approved' and RID = '" & rec.Item("RID").ToString & "') act " & _
        '            "left outer join " & _
        '            "(select RID, Value from dbo.Modelling_Factors " & _
        '            "where datacut_id = '" & CaseFactor_DataCut & "' and Status = 'Approved' and RID = '" & rec.Item("RID").ToString & "') c on (act.RID = c.RID) "
        '        End If

        '        Factor = LoadDataView(Sql)

        '        If Factor.Count = 0 Then
        '            ProviderFactor = "0"
        '            TheFactor = 0
        '        Else
        '            ProviderFactor = Factor(0).Item("value").ToString
        '            TheFactor = Factor(0).Item("value")
        '        End If

        '        'check to see if the allocations is different from what it is curretly in the allocation table
        '        FinalResult = Math.Round(IIf(IsDBNull(rec.Item("Final Result")), 0, rec.Item("Final Result")), 0)
        '        Learners = Math.Round(IIf(IsDBNull(rec.Item("Learners")), 0, rec.Item("Learners")), 0)

        '        Sql = "select Allocation, Learner_Numbers, Provider_Factor, Rate from allocations where Status = 'Edit' and Period_ID = " & PeriodID & " and Allocation_ID = '" & Allocation_ID & "' and RID = '" & rec.Item("RID").ToString & "'"
        '        ACheck = LoadDataView(Sql)

        '        If ACheck.Count <> 0 Then
        '            CurrentAllocation = ACheck(0).Item("Allocation")
        '            CurrentLearners = ACheck(0).Item("Learner_Numbers")
        '            CurrentFactor = ACheck(0).Item("Provider_Factor")
        '            CurrentRate = ACheck(0).Item("Rate")
        '        Else
        '            CurrentAllocation = 0
        '            CurrentLearners = 0
        '            CurrentFactor = 0
        '            CurrentRate = 0
        '        End If

        '        If Math.Round(FinalResult) <> Math.Round(CurrentAllocation) Or Learners <> CurrentLearners Or Math.Round(CurrentFactor, 3) <> Math.Round(TheFactor, 3) Or CurrentRate <> Rate Then
        '            'Set all allocations for this allocation_id and RID to History
        '            Sql = "update Allocations" & _
        '                 " set Status = 'History' " & _
        '                 " where Period_ID = " & PeriodID & " and Allocation_ID = '" & Allocation_ID & "' and RID = '" & rec.Item("RID").ToString & "'"
        '            RunNonQuery(Sql)

        '            Sql = "insert into Allocations values (" & _
        '                   PeriodID & ", '" & rec.Item("RID").ToString & "', '" & Allocation_ID & "', " & TheDate & ", '" & CurrentUser.Name & "', " & _
        '                   FinalResult & ", " & Rate.ToString & ", " & ProviderFactor & ", " & Learners & ", 'Approved', '" & TheNotes & "')"
        '            RunNonQuery(Sql)

        '            'now update the payment file
        '            'Only update this if the actual allocation has changed
        '            If Math.Round(FinalResult) <> Math.Round(CurrentAllocation) Then
        '                UpdateActivePayments(PeriodID, rec.Item("RID").ToString, Allocation_ID)
        '            End If
        '        Else
        '            'set it back to approved, because it has not changed
        '            Sql = "update Allocations" & _
        '                 " set Status = 'Approved' " & _
        '                 " where Period_ID = " & PeriodID & " and Allocation_ID = '" & Allocation_ID & "' and RID = '" & rec.Item("RID").ToString & "' and Status = 'Edit'"
        '            RunNonQuery(Sql)
        '        End If

        '    Next

        '    ProgressBarForm.Close()

        'End If

        ''run through all allocations that are left in edit and turn them back into history and then also run the updateactivepayments for them

        'Sql = "select * from allocations where status = 'Edit'"

        'TheData = LoadDataView(Sql)

        ''Now set all the allocations that are left in Edit to History
        'Sql = "update Allocations set Status = 'History' where Status = 'Edit' and Allocation_ID = '" & Allocation_ID & "' and Period_ID = " & PeriodID

        'Job("Setting Allocations to History...")
        'RunNonQuery(Sql)

        'For Each rec In TheData
        '    UpdateActivePayments(PeriodID, rec.Item("RID").ToString, rec.Item("Allocation_ID").ToString)
        'Next

        'HideLoadingForm()

        Job("Ready")




    End Sub

    Private Sub ApplyButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplyButton.Click

        If CurrentUser.Level <> 5 Then
            MessageBox.Show("You do not have access rights to apply allocations.", "No access", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        ApplyAllocationDialog.Text = "Apply " & SelectedLabel.Text

        If ApplyAllocationDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim sql As String
            Dim BView As DataView
            Dim BudgetID As String
            Dim AllocationID As String


            With ApplyAllocationDialog
                AllocationID = AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString
                sql = "select Budget_ID from allocation_Lines where allocation_id = '" & AllocationID & "'"
                BView = LoadDataView(sql)
                BudgetID = BView(0).Item("Budget_ID").ToString

                If MessageBox.Show("You are about to apply this allocation line.  Once started it cannot be undone.  Are you sure?", "Are you sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                    ApplyAllocation(BudgetID, AllocationID, .PeriodBox.SelectedValue.ToString, .FactorBox.SelectedValue.ToString, .CaseBox.SelectedValue.ToString, .RateBox.Text, .NotesBox.Text)
                End If
            End With

        End If

    End Sub

    Private Sub ViewbyBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ViewbyBox.SelectedIndexChanged
        ProductGrid.DataSource = Nothing
        ProductGrid.Rows.Clear()
        ProductGrid.Refresh()
        ProductView.Nodes.Clear()

        StagesGrid.DataSource = Nothing
        StagesGrid.Rows.Clear()
        StagesGrid.Refresh()

        SetToolButtons(False)

        LoadScenarioBox()

        SetStageButtons()
    End Sub

    Private Sub SetToolButtons(ByVal Enabled As Boolean)
        Button1.Enabled = True
        Button2.Enabled = Enabled

        ToolStripButton1.Enabled = Enabled
        Button3.Enabled = Enabled

        If Enabled = True Then
            If CurrentUser.Level = 5 Then
                CasesButton.Enabled = Enabled
            End If

            If CurrentUser.Level > 3 Then
                ReportDesignerBut.Enabled = Enabled
            End If

            If ItsMine Then
                DeleteScenarioBut.Enabled = Enabled
                PublishBut.Enabled = Enabled
            End If
        Else
            If CurrentUser.Level < 5 Then
                CasesButton.Enabled = Enabled
            End If
            ReportDesignerBut.Enabled = Enabled
            DeleteScenarioBut.Enabled = Enabled
            PublishBut.Enabled = Enabled
        End If

        If UserFormCheck(Me.Name) = "R" Then

            Button1.Enabled = False
            Button2.Enabled = False
            DeleteScenarioBut.Enabled = False
            ScenFolderBut.Enabled = False
            PublishBut.Enabled = False
            ReportDesignerBut.Enabled = False
            ApplyButton.Enabled = False
            AddBut.Enabled = False
            CasesButton.Enabled = False
            EditBut.Enabled = False
            DeleteBut.Enabled = False
            AddProdBut.Enabled = False
            ProductEditBut.Enabled = False
            ProdDelBut.Enabled = False
            AddFolderBut.Enabled = False
            DeleteFolderBut.Enabled = False
            CreateDataCutsToolStripMenuItem.Enabled = False
            
        End If

    End Sub

    Private Sub ScenFolderBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ScenFolderBut.Click
        Dim FolderName As String = ""
        Dim sql As String = ""

        If ScenarioBox.SelectedValue = Nothing Then Exit Sub


        If ScenarioFolderDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            If ScenarioFolderDialog.FolderBox.SelectedValue.ToString = "New0" Then
                'new folder
                FolderName = InputBox("Enter a name for the new folder", "New folder..", "")
            Else
                FolderName = ScenarioFolderDialog.FolderBox.SelectedValue
            End If

            If FolderName = "" Then Exit Sub

            sql = "update Modelling_Scenarios set Folder = '" & FolderName & "' where Scenario_ID = '" & ScenarioBox.SelectedValue & "'"
            RunNonQuery(sql)

            ScenarioFolderDialog.LoadFolders()

            Dim cScenarioID As String = ScenarioBox.SelectedValue
            Dim cFolder As String = ViewbyBox.SelectedValue

            cScenarioID = ScenarioBox.SelectedValue
            cFolder = ViewbyBox.SelectedValue

            LoadViewByBox()

            ViewbyBox.SelectedValue = cFolder
            ScenarioBox.SelectedValue = cScenarioID

        End If
    End Sub

    Private Sub HideLinesBox_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles HideLinesBox.CheckedChanged

        Try
            LoadAllocationsLineGrid()
        Catch ex As Exception

        End Try


    End Sub
End Class
